﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DbfViewer.Models
{
	public static class SqlGenerator
	{
		public static string GenerateInsertQuery(string tableName, DataTable table)
		{
			if (!ValidData(tableName, table))
			{
				return null;
			}
			var columnNamesBuilder = new StringBuilder();
			columnNamesBuilder.Append("INSERT INTO ");
			columnNamesBuilder.Append("\'");
			columnNamesBuilder.Append(tableName);
			columnNamesBuilder.Append("\'");
			columnNamesBuilder.Append(" (");
			var valuesBuilder = new StringBuilder();
			valuesBuilder.Append(" VALUES (");
			foreach (DataColumn column in table.Columns)
			{
				columnNamesBuilder.Append("\'");
				columnNamesBuilder.Append(column.ColumnName.ToString());
				columnNamesBuilder.Append("\', ");
				valuesBuilder.Append("?, ");
			}
			string columnNames = columnNamesBuilder.ToString().TrimEnd(new char[] { ',', ' ' });
			columnNames += ")";
			string values = valuesBuilder.ToString().TrimEnd(new char[] { ',', ' ' });
			values += ");";
			return columnNames + values;
		}

		private static bool ValidData(string tableName, DataTable table)
		{
			return tableName != null && tableName != String.Empty && table != null;
		}

		public static string GenerateCreateQuery(string tableName, DataTable table)
		{
			if (!ValidData(tableName, table))
			{
				return null;
			}
			var builder = new StringBuilder();
			builder.Append("CREATE TABLE ");
			builder.Append("\'");
			builder.Append(tableName);
			builder.Append("\'");
			builder.Append(" (");
			foreach (DataColumn column in table.Columns)
			{
				builder.Append("\'");
				builder.Append(column.ColumnName);
				builder.Append("\'");
				builder.Append(" ");
				builder.Append(GetDbfType(column));
				builder.Append(", ");
			}

			string createQuery = builder.ToString().TrimEnd(new char[] { ',', ' ' });
			createQuery += ");";
			return createQuery;
		}

		private static int GetRowLength(DataRow row, DataColumn column)
		{
			return row[column.ColumnName.ToString()].ToString().Trim().Length;
		}

		private static string GetDbfType(DataColumn column)
		{
			return GetDbfType(column.DataType, GetMaxLength(column));
		}

		private static string GetDbfType(Type type, int maxLength)
		{
			switch (type.ToString())
			{
				case "System.Boolean":
					return "L";
				case "System.DateTime":
					return "D";
				case "System.SByte":
				case "System.Byte":
				case "System.Int16":
				case "System.UInt16":
				case "System.Int32":
				case "System.UInt32":
				case "System.Int64":
				case "System.UInt64":
					return "N";
				case "System.Single":
				case "System.Double":
				case "System.Decimal":
					return "F(16,4)";
				// остальное сохраняем как строку
				default:
					return "C(" + maxLength + ")";
			}
		}

		private static int GetMaxLength(DataColumn column)
		{
			int maxLength = 0;
			foreach (DataRow row in column.Table.Rows)
			{
				if (maxLength < GetRowLength(row, column))
				{
					maxLength = GetRowLength(row, column);
				}
			}
			return maxLength;
		}
	}
}
